package cn.com.cifi.mars.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.com.cifi.mars.bean.vo.supply.ComfirmBaseValueVO;
import cn.com.cifi.mars.bean.vo.supply.SupplyPlanVersionCompareExcelVO;
import cn.com.cifi.mars.bean.vo.supply.YearMonthComfirmVO;
import cn.com.cifi.mars.constant.Constants;
import cn.com.cifi.mars.constant.OrgTypeEunm;

public class SupplyCompareReportTemplate {
    
        private static final String TEMPLATE_PATH = "/template/supply_compare_report_template.xlsx";
        
        private static final String CURRENT_PATTERN = "{current}";

	    private XSSFWorkbook workbook;

	    private XSSFSheet sheet;

	    /**
	     * 默认sheet索引
	     */
	    private int defaultSheetIndex = 0;

	    /**
	     * 数据开始行
	     */
	    private int dataBeginRow = 3;
	    
	    /**
	     * 数据模板行
	     */
	    /**
	     * 常规样式-不带底色
	     */
	    private XSSFCellStyle baseCellStyle;
	    /**
	     * 样式（数字格式-0位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle1;
	    /**
	     * 样式（数字格式-2位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle2;
	    /**
	     * 样式（百分比格式-2位小数，居右）-不带底色
	     */
	    private XSSFCellStyle cellStyle3;

	    /**
	     * 当前行标
	     */
	    private int currentDataRowCursor = dataBeginRow;
	    
	    /**
	     * 权益比例
	     */
	    private static final String EQUITY_RATIO  = "equityRatio";
	    /**
	     * 数字-两位小数
	     */
		private static final String NUMERIC_TWO  = "numericByTwo";
		/**
	     * 数字-0位小数
	     */
		private static final String NUMERIC_ZERO  = "numericByZero";
	    
	    public SupplyCompareReportTemplate(String deadline) {
	        try {
	            InputStream tempInputStream = SupplyCompareReportTemplate.class.getResourceAsStream(TEMPLATE_PATH);
	            if (tempInputStream != null) {
	                workbook = new XSSFWorkbook(tempInputStream);
	                sheet = workbook.getSheetAt(defaultSheetIndex);
	                setSheetDate(deadline);
	                setCurrentDate(LocalDateTime.now());
	                //常规
	                baseCellStyle=sheet.getRow(dataBeginRow).getCell(0).getCellStyle();
	                //数字格式-0位小数
	                cellStyle1=sheet.getRow(dataBeginRow).getCell(20).getCellStyle();
	                //数字格式-2位小数
	                cellStyle2=sheet.getRow(dataBeginRow).getCell(22).getCellStyle();
	                //百分比格式-2位小数
	                cellStyle3=sheet.getRow(dataBeginRow).getCell(8).getCellStyle();
	            }
	        } catch (Exception e) {
	            throw new RuntimeException(e);
	        }
	    }
	    
		public void setSheetDate(String deadline) {
	        workbook.setSheetName(defaultSheetIndex, workbook.getSheetName(defaultSheetIndex).replace("供货对比报表", "供货对比报表-截止".concat(deadline.replace("-", "")).concat("-").concat(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHH")))));
		}
		
		public void setCurrentDate(LocalDateTime date) {
			XSSFRow row = sheet.getRow(0);
	        if (row != null) {
	            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
	            	XSSFCell cell = row.getCell(i);
	                if (cell != null && cell.getStringCellValue().contains(CURRENT_PATTERN)) {
	                    cell.setCellValue(cell.getStringCellValue().replace(CURRENT_PATTERN, String.valueOf(date.getYear())));
	                }
	            }
	        }
	    }

	    /**
	     * 批量插入行
	     *
	     * @param list
	     */
	    public synchronized void appendRows(List<SupplyPlanVersionCompareExcelVO> list) {
	    	List<SupplyPlanVersionCompareExcelVO> resList = Collections.synchronizedList(new LinkedList<>());
	    	List<SupplyPlanVersionCompareExcelVO> lastList = Collections.synchronizedList(new LinkedList<>());
	    	for (SupplyPlanVersionCompareExcelVO vo : list) {
	    		if(vo.getType().equals(OrgTypeEunm.ORG_WANDA_GROUP.getKey())) {
	    			lastList.add(vo);
	    		}else {
	    			resList.add(vo);
	    		}
	    	}
	    	lastList.addAll(resList);
	    	
	    	for (SupplyPlanVersionCompareExcelVO vo : lastList) {
	            this.appendRow(vo);
	        }
	    }
	    
	    public synchronized void appendRow(SupplyPlanVersionCompareExcelVO vo) {
	    	XSSFRow row = sheet.getRow(currentDataRowCursor);
	        if (row == null) {
	            row = sheet.createRow(currentDataRowCursor);
	        }
	        row.setHeightInPoints(Constants.HEIGHT_POINTS);
	        
	        AtomicInteger index = new AtomicInteger(-1);
	        //层级
	        this.appendCell(row, index.addAndGet(1),OrgTypeEunm.getValueByKey(vo.getType()),"");
	        //区域
	        this.appendCell(row, index.addAndGet(1),vo.getAreaName(),"");
	        //城市公司
	        this.appendCell(row, index.addAndGet(1),vo.getCityName(),"");
	        //项目信息
	        this.appendCell(row, index.addAndGet(1),vo.getProjectName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProjectCode(),"");
	        this.appendCell(row, index.addAndGet(1),handleIsFirst(vo.getIsFirst()),"");
	        //分期信息
	        this.appendCell(row, index.addAndGet(1),vo.getStageName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getStageCode(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getEquityRatio(),EQUITY_RATIO);
	        this.appendCell(row, index.addAndGet(1),vo.getTradersWay(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getIsMergeTable(),"");
	        this.appendCell(row, index.addAndGet(1),handlePendingHomeSale(vo.getPendingHomeSale()),"");
	        //组团信息
	        this.appendCell(row, index.addAndGet(1),vo.getGroupName(),"");
	        //产品信息
	        this.appendCell(row, index.addAndGet(1),vo.getProductTypeName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductTypeCode(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProductCode(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getBusinessTypeName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getFreeTypeName(),"");
	        this.appendCell(row, index.addAndGet(1),vo.getProTypeName(),"");
	        
	        //年度对比
	        YearMonthComfirmVO yearValue = vo.getYearValue();
	        if(yearValue != null) {
	        	ComfirmBaseValueVO budgetValue = yearValue.getBudgetValue();
	        	ComfirmBaseValueVO dynamicValue = yearValue.getDynamicValue();
	        	ComfirmBaseValueVO differenceValue = yearValue.getDifferenceValue();
	        	
        		makeColumn(row, index, budgetValue, dynamicValue, differenceValue);
	        }
	        //上半年月度对比
	        List<YearMonthComfirmVO> firstMonthlyValue = vo.getFirstMonthlyValue();
	        if(CollectionUtils.isNotEmpty(firstMonthlyValue)) {
	        	for(YearMonthComfirmVO firstMonthlyValueVo : firstMonthlyValue) {
	        		ComfirmBaseValueVO budgetValue = firstMonthlyValueVo.getBudgetValue();
		        	ComfirmBaseValueVO dynamicValue = firstMonthlyValueVo.getDynamicValue();
		        	ComfirmBaseValueVO differenceValue = firstMonthlyValueVo.getDifferenceValue();
		        	
		        	makeColumn(row, index, budgetValue, dynamicValue, differenceValue);
	        	}
	        }
	        //上半年度对比
	        YearMonthComfirmVO firstHalfYearValue = vo.getFirstHalfYearValue();
	        if(firstHalfYearValue != null) {
	        	ComfirmBaseValueVO budgetValue = firstHalfYearValue.getBudgetValue();
	        	ComfirmBaseValueVO dynamicValue = firstHalfYearValue.getDynamicValue();
	        	ComfirmBaseValueVO differenceValue = firstHalfYearValue.getDifferenceValue();
	        	
	        	makeColumn(row, index, budgetValue, dynamicValue, differenceValue);
	        }
	        //下半年度对比
	        YearMonthComfirmVO secondHalfYearValue = vo.getSecondHalfYearValue();
	        if(secondHalfYearValue != null) {
	        	ComfirmBaseValueVO budgetValue = secondHalfYearValue.getBudgetValue();
	        	ComfirmBaseValueVO dynamicValue = secondHalfYearValue.getDynamicValue();
	        	ComfirmBaseValueVO differenceValue = secondHalfYearValue.getDifferenceValue();
	        	
	        	makeColumn(row, index, budgetValue, dynamicValue, differenceValue);
	        }
	        
	        //下半年月度对比
	    	List<YearMonthComfirmVO> secondMonthlyValue = vo.getSecondMonthlyValue();
	    	if(CollectionUtils.isNotEmpty(secondMonthlyValue)) {
	        	for(YearMonthComfirmVO secondMonthlyValueVo : secondMonthlyValue) {
	        		ComfirmBaseValueVO budgetValue = secondMonthlyValueVo.getBudgetValue();
		        	ComfirmBaseValueVO dynamicValue = secondMonthlyValueVo.getDynamicValue();
		        	ComfirmBaseValueVO differenceValue = secondMonthlyValueVo.getDifferenceValue();
		        	
		        	makeColumn(row, index, budgetValue, dynamicValue, differenceValue);
	        	}
	        }
	        currentDataRowCursor++;
	    }

		private void makeColumn(XSSFRow row, AtomicInteger index, ComfirmBaseValueVO budgetValue, ComfirmBaseValueVO dynamicValue,
				ComfirmBaseValueVO differenceValue) {
			this.appendCell(row, index.addAndGet(1),intToString(budgetValue.getRoomNum()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),intToString(budgetValue.getParkingNum()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(budgetValue.getSaleArea()),NUMERIC_TWO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(budgetValue.getHouseAvgPrice()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(budgetValue.getParkingAvgPrice()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(BigDecimalADD(4, budgetValue.getHousePrice(), budgetValue.getParkingPrice()))),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(budgetValue.getHousePrice())),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(budgetValue.getParkingPrice())),NUMERIC_ZERO);
			
			this.appendCell(row, index.addAndGet(1),intToString(dynamicValue.getRoomNum()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),intToString(dynamicValue.getParkingNum()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(dynamicValue.getSaleArea()),NUMERIC_TWO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(dynamicValue.getHouseAvgPrice()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(dynamicValue.getParkingAvgPrice()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(BigDecimalADD(4, dynamicValue.getHousePrice(), dynamicValue.getParkingPrice()))),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(dynamicValue.getHousePrice())),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(dynamicValue.getParkingPrice())),NUMERIC_ZERO);
			
			this.appendCell(row, index.addAndGet(1),intToString(differenceValue.getRoomNum()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),intToString(differenceValue.getParkingNum()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(differenceValue.getSaleArea()),NUMERIC_TWO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(differenceValue.getHouseAvgPrice()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(differenceValue.getParkingAvgPrice()),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(BigDecimalADD(4, differenceValue.getHousePrice(), differenceValue.getParkingPrice()))),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(differenceValue.getHousePrice())),NUMERIC_ZERO);
			this.appendCell(row, index.addAndGet(1),bigDecimalToString(fmtToWanUnit(differenceValue.getParkingPrice())),NUMERIC_ZERO);
		}
	    
	    
	    private String handleIsFirst(String value) {
	    	if(StrUtils.isEmpty(value)) {
	    		return "";
	    	}
	    	return value.equals("0") ? "续销" : "首开";
	    }
	    
	    private String handlePendingHomeSale(String value) {
	    	if(StrUtils.isEmpty(value)) {
	    		return "";
	    	}
	    	return value.equals("0") ? "非现房销售" : "现房销售";
	    }

	    public synchronized void appendCell(XSSFRow row, int index, String value,String type) {
	    	XSSFCell cell = row.getCell(index);
	        if (cell == null) {
	            cell = row.createCell(index);
	        }
	        switch (type) {
			case NUMERIC_ZERO:
				cell.setCellStyle(cellStyle1);
				if(StrUtils.isNotEmpty(value)) {
					cell.setCellValue(Double.valueOf(value).doubleValue());
				}else {
					cell.setCellValue(value);
				}
				break;
			case NUMERIC_TWO:
				cell.setCellStyle(cellStyle2);
				if(StrUtils.isNotEmpty(value)) {
					cell.setCellValue(Double.valueOf(value).doubleValue());
				}else {
					cell.setCellValue(value);
				}
				break;
			case EQUITY_RATIO:
				cell.setCellStyle(cellStyle3);
				if(StrUtils.isNotEmpty(value)) {
					cell.setCellValue(getDouble(value));
				}else {
					cell.setCellValue(value);
				}
				break;
			default:
				cell.setCellStyle(baseCellStyle);
				cell.setCellValue(value);
				break;
			}
	    }
	    
	    private double getDouble(String value) {
	    	BigDecimal bigDecimal = MathUtils.newBigDecimal(value);
            return bigDecimal.divide(MathUtils.NUM_100,2, RoundingMode.HALF_UP).doubleValue();
	    }
	    
	    public static BigDecimal BigDecimalADD(int scale,BigDecimal v1, BigDecimal v2) {
			if(v1 == null && v2 == null) {
				return BigDecimal.ZERO;
			}else {
				if(v1 == null) {
					v1 = BigDecimal.ZERO;
				}
				if(v2 == null) {
					v2 = BigDecimal.ZERO;
				}
				return v1.add(v2).setScale(scale, BigDecimal.ROUND_HALF_UP);
			}
		}
	    
	    public static BigDecimal fmtToWanUnit(BigDecimal val) {
	    	if(val == null) {
	    		return BigDecimal.ZERO;
	    	}
			return val.divide(MathUtils.NUM_10000,0, RoundingMode.HALF_UP);
		}
	    
	    public static String intToString(Integer val) {
	    	if(val == null) {
	    		return "0";
	    	}
	    	return String.valueOf(val);
	    }
	    
	    public static String bigDecimalToString(BigDecimal val) {
	    	if(val == null) {
	    		return "0.00";
	    	}
	    	return val.toString();
	    }
	    
	    
	    public void write(OutputStream outputStream) throws IOException {
	        workbook.write(outputStream);
	    }
	    
}
